How to do a Pareto Analysis in Excel in 5 simple steps

Calculate the relative effect of each cause by dividing its corresponding effect by the total effect calculated in STEP 1.

In our example, it means dividing the corresponding “Product Sales” by the “Total Sales” for each product. When typing the formula for the first relative effect, ensure that you have no “$” symbol for the cause’s effect (here “C8” for the first product), but you do have a “$” symbol for the total effect’s cell reference (here $C$5).  An Excel cell reference with the “$” symbol is called an absolute reference. An absolute reference allows you to drag the formula down to the bottom of your data set and keep this reference unchanged. This is exactly what we want in this case for the total effect. Dragging down the first formula you entered to the bottom of the Excel data set is the fastest solution to replicate the relative effect formula for all causes. To do so, select the first formula you entered at the top of the Excel data set and drag it to the bottom. For clarification purposes, please note that in this Excel Pareto Analysis Tutorial, the word “Impact” is used interchangeably with the word “Effect”.